package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.OvertimeDao;
import com.mp.approvalreq.entity.Overtime;
import com.plusmoney.util.DateTimeHelper;
import com.plusmoney.util.SQLLogger;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class OvertimeDaoImpl extends AbstractDao implements OvertimeDao {

    private static final Logger logger = LoggerFactory.getLogger(OvertimeDaoImpl.class);

    private Connection connection;

    public OvertimeDaoImpl(Connection connection) {
        this.connection = connection;
    }

    private PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    @Override
    String getQuerySql(String condition) {
        final String query = "select id, company_id, company_info_id, request_id, start_time,"
                + " end_time, duration, show_time, address, coordinate,"
                + " reason"
                + " from sys_approval_overtime";
        return String.format("%s %s", query, condition);
    }

    @Override
    Overtime getObject(ResultSet rs) throws SQLException {
        Overtime overtime = new Overtime();
        overtime.setId(rs.getInt(1));
        overtime.setCompanyId(rs.getInt(2));
        overtime.setCompanyInfoId(rs.getInt(3));
        overtime.setRequestId(rs.getInt(4));
        overtime.setStart(rs.getTimestamp(5));
        overtime.setStartTime(getTimeStr(rs.getTimestamp(5)));

        overtime.setEnd(rs.getTimestamp(6));
        overtime.setEndTime(getTimeStr(rs.getTimestamp(6)));
        overtime.setDuration(rs.getInt(7));
        overtime.setShowTime(rs.getString(8));
        overtime.setAddress(rs.getString(9));
        overtime.setCoordinate(rs.getString(10));

        overtime.setReason(rs.getString(11));
        return overtime;
    }

    private String getListQuerySql(String condition) {
        final String query = "select company_id, company_info_id, request_id, start_time,  end_time,"
                + " duration, address, coordinate, reason"
                + " from sys_approval_overtime";
        return String.format("%s %s", query, condition);
    }

    private Overtime getListObject(ResultSet rs) throws SQLException {
        Overtime overtime = new Overtime();
        overtime.setCompanyId(rs.getInt(1));
        overtime.setCompanyInfoId(rs.getInt(2));
        overtime.setRequestId(rs.getInt(3));
        overtime.setStart(rs.getTimestamp(4));
        overtime.setStartTime(getTimeStr(rs.getTimestamp(4)));
        overtime.setEnd(rs.getTimestamp(5));
        overtime.setEndTime(getTimeStr(rs.getTimestamp(5)));

        overtime.setDuration(rs.getInt(6));
        overtime.setAddress(rs.getString(7));
        overtime.setCoordinate(rs.getString(8));
        overtime.setReason(rs.getString(9));
        return overtime;
    }

    @Override
    public List<Overtime> selectOvertimesByRequestId(Integer requestId) throws SQLException {
        List<Overtime> list = new ArrayList<>();
        String sql = getQuerySql("where request_id = ?");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public void insertBatchOvertimes(List<Overtime> overtimes) throws SQLException {
        String sql = "insert into sys_approval_overtime set"
                + " company_id = ?, company_info_id = ?, request_id = ?, start_time = ?, end_time = ?,"
                + " duration = ?, show_time = ?, address = ?, coordinate = ?, reason = ?,"
                + " approval_num = ?, create_time = now()";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Overtime overtime : overtimes) {
                String startTime = DateTimeHelper.format(overtime.getStart(), DateTimeHelper.DEFAULT_PATTERN);
                String endTime = DateTimeHelper.format(overtime.getEnd(), DateTimeHelper.DEFAULT_PATTERN);
                ps.setInt(1, overtime.getCompanyId());
                ps.setInt(2, overtime.getCompanyInfoId());
                ps.setInt(3, overtime.getRequestId());
                ps.setString(4, startTime);
                ps.setString(5, endTime);

                ps.setInt(6, overtime.getDuration());
                ps.setString(7, overtime.getShowTime());
                ps.setString(8, overtime.getAddress());
                ps.setString(9, overtime.getCoordinate());
                ps.setString(10, overtime.getReason());

                ps.setString(11, overtime.getApprovalNum());
                logger.info(SQLLogger.logSQL(sql, overtime.getCompanyId(), overtime.getCompanyInfoId(), overtime.getRequestId(),
                        startTime, endTime, overtime.getDuration(), overtime.getShowTime(), overtime.getAddress(), overtime.getCoordinate(),
                        overtime.getReason(), overtime.getApprovalNum()));

                ps.addBatch();
            }

            ps.executeBatch();
        }
    }

    @Override
    public List<Overtime> selectCompanyOvertimesByDateScope(Integer companyId, String startTime, String endTime) throws SQLException {
        List<Overtime> list = new ArrayList<>();
        String sql = getListQuerySql("where company_id = ? and not(end_time <= ? or start_time >= ?) and state = 1");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, startTime);
            ps.setString(3, endTime);
            logger.info(SQLLogger.logSQL(sql, companyId, startTime, endTime));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getListObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Overtime> selectBatchUserOvertimesByDateScope(Integer companyId, String companyInfoIds, String startTime, String endTime) throws SQLException {
        List<Overtime> list = new ArrayList<>();
        String sql = getListQuerySql("where company_id = ? and company_info_id in(" + companyInfoIds + ")"
                + " and not(end_time <= ? or start_time >= ?) and state = 1");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setString(2, startTime);
            ps.setString(3, endTime);
            logger.info(SQLLogger.logSQL(sql, companyId, startTime, endTime));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getListObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public void deleteOvertimeByRequestId(Integer requestId, Integer companyId) throws SQLException {
        String sql = "update sys_approval_overtime set state = 2, update_time = now() where request_id = ? and company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyId));

            ps.executeUpdate();
        }
    }

    @Override
    public Map<Integer, List<Overtime>> selectOvertimesByRequestIds(String requestIds) throws SQLException {
        Map<Integer, List<Overtime>> map = new HashMap<>();
        if (StringUtils.isEmpty(requestIds)) {
            return map;
        }
        String sql = getQuerySql("where request_id in(" + requestIds + ")");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Overtime overtime = getObject(rs);
                    if (!map.containsKey(overtime.getRequestId())) {
                        map.put(overtime.getRequestId(), new ArrayList<>());
                    }
                    map.get(overtime.getRequestId()).add(overtime);
                }
            }
        }
        return map;
    }
}
